//201902104011 王雪梦
package bysj.dao;
import bysj.domain.School;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class SchoolDao {
    private static SchoolDao schoolDao =null;

    private SchoolDao() {
    }

    public static SchoolDao getInstance() {
        if(schoolDao==null){
            schoolDao = new SchoolDao();
        }
        return schoolDao;
    }

    public Collection<School> findAll(Connection connection,List<Condition>conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        Collection<School> schools = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM school");
        int totalNum = CommonDao.getInstance().count(connection,"school");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String description = resultSet.getString(2);
            String no = resultSet.getString(3);
            String remarks = resultSet.getString(4);
            School school = new School(id,description,no,remarks);
            //将school增加到schools集合
            schools.add(school);
            }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement, connection);
        return schools;
    }

    public static School find(int id,Connection connection) throws SQLException {
        School school = new School(id,"description","no","remarks");
        //创建sql语句
        String school_sql = "SELECT * FROM school WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(school_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
            //为school对象的description赋值
            school.setDescription(resultSet.getString(2));
            //为school对象的no赋值
            school.setNo(resultSet.getString(3));
            //为school对象的remarks赋值
            school.setRemarks(resultSet.getString(4));
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return school;
    }

    public boolean update(School school, Connection connection) throws SQLException {
        //创建sql语句
        String school_sql = "update school set no=?,description=?,remarks=? where id="+school.getId();
        //在该连接创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(school_sql);
        //为预编译参数赋值
        preparedStatement.setString(1,school.getNo());
        preparedStatement.setString(2,school.getDescription());
        preparedStatement.setString(3,school.getRemarks());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return affectedRowNum>0;
    }

    public static Integer add(School school, Connection connection) throws SQLException {
        //创建sql语句
        String school_sql = "INSERT INTO school(description,no,remarks) Values"+"(?,?,?)";
        System.out.println(school.getDescription());
        //在该连接创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(school_sql, Statement.RETURN_GENERATED_KEYS);
        //为预编译参数赋值
        preparedStatement.setString(1,school.getDescription());
        preparedStatement.setString(2,school.getNo());
        preparedStatement.setString(3,school.getRemarks());
        //执行预编译对象的executeUpdate方法，获得添加的记录行数
        int affectedRowNum = preparedStatement.executeUpdate();
        int idOfAddedUser = 0;
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if(resultSet.next()){
            //读取第一个主键
            idOfAddedUser = resultSet.getInt(1);
        }
        JdbcHelper.close(preparedStatement);
        //如果添加失败 返回null，添加成功，返回school对象的id
        if(affectedRowNum==0){
            return null;
        }else {
            return idOfAddedUser;
        }
    }

    public boolean delete(int id,Connection connection) throws SQLException {
        //创建sql语句
        String school_sql = "delete from school where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(school_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return affectedRowNum>0;
    }
}
